package itd.db;

import itd.domain.Item;
import itd.domain.Product;
import itd.domain.Shop;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;
import itd.exceptions.*;

public class DBConnection {

	public static Vector getSimilProducts(BigDecimal idOrig) throws ITDException{
		Vector vcProducts  = new Vector();
		Connection con = null;

		try {
			con = DBPool.getConnection();

			PreparedStatement pstmt = null;
			String SQL = "SELECT id_producto_2 " +
						 "FROM relacion where id_producto_1 = ? ";
			pstmt = con.prepareStatement(SQL);
			pstmt.setBigDecimal(1, idOrig);

			ResultSet res = pstmt.executeQuery();

			while (res.next()) {
				BigDecimal idProd2 = new BigDecimal(res.getInt("id_producto_2"));
				Product product = getProductDetail(idProd2);
				vcProducts.add(product);
			}
			res.close();
			pstmt.close();
		} catch (Exception e) {

			throw new ITDException(e);
		} finally {
		      if (con != null) DBPool.returnConnection(con);
	    }
		return vcProducts;
	}
	public static Vector getProductsList() throws ITDException{
		Vector vcProducts  = new Vector();
		Connection con = null;

		try {
			con = DBPool.getConnection();

			PreparedStatement pstmt = null;
			String SQL = "SELECT * " +
						 "FROM PRODUCTO ";
			pstmt = con.prepareStatement(SQL);

			ResultSet res = pstmt.executeQuery();
			while (res.next()) {
				Product product = new Product(res.getBigDecimal("id_producto"),
										res.getString("descripcion"),
										res.getString("marca"),
										res.getString("imagen"));
				product.setModel(res.getString("modelo"));
				vcProducts.add(product);
			}
			res.close();
			pstmt.close();
		} catch (Exception e) {

			throw new ITDException(e);
		} finally {
		      if (con != null) DBPool.returnConnection(con);
	    }
		return vcProducts;
	}

	public static Product getProductDetail(BigDecimal id) throws ITDException{
		Product product = null;
		Connection con = null;

		try {
			con = DBPool.getConnection();

			PreparedStatement pstmt = null;
			String SQL = "SELECT * " +
						 "FROM PRODUCTO WHERE id_producto= ?";
			pstmt = con.prepareStatement(SQL);
			pstmt.setBigDecimal(1, id);

			ResultSet res = pstmt.executeQuery();

			if (res.next()) {
				product = new Product(res.getBigDecimal("id_producto"),
										res.getString("descripcion"),
										res.getString("marca"),
										res.getString("imagen"));
				product.setModel(res.getString("modelo"));

			}
			res.close();
			pstmt.close();
		} catch (Exception e) {

			throw new ITDException(e);
		} finally {
		      if (con != null) DBPool.returnConnection(con);
	    }
		return product;
	}

	public static Vector getProductShops(BigDecimal id) throws ITDException{
		Vector vcProducts  = new Vector();
		Connection con = null;

		try {
			con = DBPool.getConnection();

			PreparedStatement pstmt = null;
			String SQL = "SELECT * " +
						 "FROM TIENDA_PRODUCTO TP, TIENDA T  WHERE TP.ID_TIENDA = T.ID_TIENDA AND TP.ID_PRODUCTO= ? ";

			pstmt = con.prepareStatement(SQL);
			pstmt.setBigDecimal(1, id);

			ResultSet res = pstmt.executeQuery();
			while (res.next()) {
				Shop product = new Shop(res.getBigDecimal("id_tienda"),
										res.getString("logo"),
										res.getString("url"),
										res.getBigDecimal("precio"));
				vcProducts.add(product);
			}
			res.close();
			pstmt.close();
		} catch (Exception e) {

			throw new ITDException(e);
		} finally {
		      if (con != null) DBPool.returnConnection(con);
	    }
		return vcProducts;
	}
	public static Vector getItemsByProduct(BigDecimal idProd) throws ITDException{
		Vector vcItems  = new Vector();
		Connection con = null;

		try {
			con = DBPool.getConnection();

			PreparedStatement pstmt = null;
			String SQL = "SELECT IP.ID_ITEM,NOMBRE,TIPO, VALOR,DESCRIPCION FROM ITEM_PRODUCTO IP , ITEM I  WHERE IP.ID_PRODUCTO = ? AND I.ID_ITEM = IP.ID_ITEM";
			pstmt = con.prepareStatement(SQL);
			pstmt.setBigDecimal(1, idProd);

			ResultSet res = pstmt.executeQuery();
			while (res.next()) {
				Item item = new Item (res.getBigDecimal("id_item"),
										res.getString("nombre"),
										res.getString("tipo"),
										res.getString("descripcion"),
										res.getString("valor"));
				vcItems.add(item);
			}
			res.close();
			pstmt.close();
		} catch (Exception e) {

			throw new ITDException(e);
		} finally {
		      if (con != null) DBPool.returnConnection(con);
	    }
		return vcItems;
	}

}
